package com.eflying.book.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.eflying.book.bean.Book;
import com.eflying.pager.Expression;
import com.eflying.pager.PageBean;
import com.eflying.pager.PageConstants;

import cn.itcast.jdbc.TxQueryRunner;

public class BookDao {
	QueryRunner qr = new TxQueryRunner();
	/**
	 * 按分类查询
	 * @throws SQLException 
	 */
	public PageBean<Book> findByCategory(String cid , int pc) throws SQLException{
		List<Expression> list = new ArrayList<Expression>();
		list.add(new Expression("cid", "=", cid));
		return findByCriteria(list , pc);
	}
	/**
	 * 按书名模糊查询
	 * @throws SQLException 
	 */
	public PageBean<Book> findByBname(String bname , int pc) throws SQLException{
		List<Expression> list = new ArrayList<Expression>();
		list.add(new Expression("bname" , "like" , "%" + bname + "%"));
		return findByCriteria(list, pc);
	}
	/**
	 * 按作者，出版社，多条件组合等。。。
	 */
	public PageBean<Book> findByAuthor(String author , int pc) throws SQLException{
		List<Expression> list = new ArrayList<Expression>();
		list.add(new Expression("author" , "like" , "%" + author + "%"));
		return findByCriteria(list, pc);
	}
	public PageBean<Book> findByPress(String press , int pc) throws SQLException{
		List<Expression> list = new ArrayList<Expression>();
		list.add(new Expression("press" , "like" , "%" + press + "%"));
		return findByCriteria(list, pc);
	}
	public PageBean<Book> findByCombination(Book criteria , int pc) throws SQLException{
		List<Expression> list = new ArrayList<Expression>();
		list.add(new Expression("bname" , "like" , "%" + criteria.getBname() + "%"));
		list.add(new Expression("author" , "like" , "%" + criteria.getAuthor() + "%"));
		list.add(new Expression("press" , "like" , "%" + criteria.getPress() + "%"));
		return findByCriteria(list, pc);
	}
	
	/**
	 * 模仿Hibernate，做一个通用的查询方法
	 * @throws SQLException 
	 * 
	 */
	public PageBean<Book> findByCriteria(List<Expression> expList , int pc) throws SQLException{
		/*
		 * 1.得到ps
		 */
		int ps = PageConstants.BOOK_PAGE_SIZE;//书本每页的记录数
		/*
		 * 2.得到tr,首先通过listExp生成where子句
		 */
		StringBuilder where = new StringBuilder(" where 1=1");
		List<Object> params = new ArrayList<Object>();
		for(Expression exp : expList){
			where.append(" and ").append(exp.getName()).append(" ").
			append(exp.getOperator()).append(" ");
			if(!exp.getOperator().equalsIgnoreCase("is null")){
				where.append("?");
				params.add(exp.getValue());
			}
		}
		//开始弄tr
		String sql = "select count(1) from t_book" + where;
		Number number = (Number)qr.query(sql, new ScalarHandler(), params.toArray());
		int tr = number.intValue();
		/*
		 * 3.得到beanList
		 */
		sql = "select * from t_book" + where +" limit ?,?";
		params.add((pc - 1) * ps);
		params.add(ps);
		List<Book> list = qr.query(sql, new BeanListHandler<Book>(Book.class), params.toArray());
		
		/*
		 * 4.封装pagebean对象返回
		 */
		PageBean<Book> pb = new PageBean<Book>();
		//url交给servlet去搞
		pb.setBeanList(list);
		pb.setPc(pc);
		pb.setPs(ps);
		pb.setTr(tr);
		
		return pb;
	}
}
